# -*- coding: utf-8 -*-
import types
import pypyodbc
import os, sys, string, getopt, re
import sqlite3
import configparser
from idna.core import unicode


def StrClean(field):
    field = str(field)
    if field == 'None':
        field = ''
    field = field.replace("'", "''").replace('\r', '').replace('\n', '').replace('\t', '').strip()
    field = field.replace('\x00', '').replace('\\x00', '')
    return field


def insertmdb(connMDB, insert_sql):
    cur = connMDB.cursor()
    try:
        cur.execute(insert_sql).commit()
        cur.close()
    except Exception as e:
        cur.close()
        print(insert_sql)
        print(e)


def ReadDB3(connDB3, connMDB, tbl_name, col_name_list):
    curDB3 = connDB3.cursor()

    select_sql = 'select '
    for col_name in col_name_list:
        select_sql = select_sql + col_name + ','
    select_sql = select_sql.strip(',') + ' from ' + tbl_name
    # print(select_sql)

    curDB3.execute(select_sql)

    cnt = 0
    while True:
        row = curDB3.fetchone()
        if not row:
            break

        # lngid = StrClean(row[0])
        # title_c = StrClean(row[1])
        # gch = StrClean(row[2])
        # showwriter = StrClean(row[3])
        # _type = StrClean(row[4])
        # language = StrClean(row[5])

        col_value_list = []

        for i in range(0, len(col_name_list)):
            col_value_list.append(StrClean(row[i]))

        insert_sql = 'INSERT INTO ' + tbl_name + '('
        for col_name in col_name_list:
            insert_sql = insert_sql + col_name + ','
        insert_sql = insert_sql.strip(',') + ') values('

        for col_value in col_value_list:
            insert_sql = insert_sql + "'" + col_value + "',"
        insert_sql = insert_sql.strip(',') + ')'

        insertmdb(connMDB, insert_sql)

    curDB3.close()
    connDB3.close()


if __name__ == "__main__":
    cf = configparser.ConfigParser()
    cf.read("config.ini")
    rootdir = cf.get("parameters", "rootdir")
    outdir = cf.get("parameters", "outdir")

    for parent, dirnames, filenames in os.walk(rootdir):
        for filename in filenames:
            if filename.endswith('.db3'):
                print(parent + '\\' + filename)
                db3path = parent + '\\' + filename
                outpath = outdir + '\\' + filename.replace('.db3', '.mdb')

                if not os.path.exists(outdir):
                    os.makedirs(outdir)

                connDB3 = sqlite3.connect(db3path)
                connDB3.text_factory = lambda x: unicode(x, "utf-8", "ignore")
                curDB3 = connDB3.cursor()

                # 获取表名
                curDB3.execute("SELECT name FROM sqlite_master WHERE type='table'")
                Tables = curDB3.fetchall()

                # 获取第一个表名
                tbl_name = Tables[0][0]
                # print(tbl_name)

                # 获取表的列名
                curDB3.execute("SELECT * FROM " + tbl_name)
                col_name_list = [tuple[0] for tuple in curDB3.description]
                # print(col_name_list)

                curDB3.close()

                # 创建mdb文件，并create table
                if not os.path.exists(outpath):
                    connMDB = pypyodbc.win_create_mdb(outpath)  # 创建access数据库
                    # DNS  = 'DRIVER={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=' + outpath
                    # conn = pypyodbc.win_connect_mdb(DNS)#连接数据库
                    cur = connMDB.cursor()

                    create_table = "create table " + tbl_name + "( "
                    for col_name in col_name_list:
                        create_table = create_table + col_name + ' memo,'
                    create_table = create_table.strip(',') + ')'
                    # print(create_table)

                    cur.execute(create_table).commit()
                    cur.close()

                    ReadDB3(connDB3, connMDB, tbl_name, col_name_list)

                    connMDB.close()
                else:
                    print('导出目录已存在目标文件！')
